package dao;

import model.LendRecordModel;
import util.WebSiteUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class LendRecordDAO {
    /**
     * 根据用户ID及书籍编号查询借书记录
     * @param userId
     * @param bookId
     * @return
     */
    public LendRecordModel getLendRecordByUserIdAndBookId(String userId, int bookId){
        String sql = "select * from lendrecord where userid =? and bookid= ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,userId);
            ps.setInt(2,bookId);
            rs = ps.executeQuery();
            while(rs.next()){
                LendRecordModel m = new LendRecordModel();
                m.setId(rs.getInt("id"));
                m.setBookId(rs.getInt("bookid"));
                m.setUserId(rs.getString("userid"));
                java.sql.Timestamp d = rs.getTimestamp("createtime");
                m.setCreateTime(new java.util.Date(d.getTime()));
                return m;
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(rs!=null){
                try{
                    rs.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }

        return null;
    }

    /**
     * 新增借书记录
     * @param model
     * @return
     */
    public boolean createLendRecord(LendRecordModel model){
        boolean rs = false;
        String sql = "insert into lendrecord(userid,bookid,createtime) values (?,?,NOW())";
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,model.getUserId());
            ps.setInt(2,model.getBookId());
            ps.execute();
            rs = true;
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        return rs;
    }

    /**
     * 删除借书记录
     * @param userId
     * @param bookId
     * @return
     */
    public boolean deleteLendRecord(String userId,int bookId){
        boolean rs = false;
        String sql = "delete from lendrecord where userid=? and bookid=? ";
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,userId);
            ps.setInt(2,bookId);
            if(ps.executeUpdate()==1){
                rs = true;
            }else{
                rs = false;
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        return rs;
    }

    /**
     * 根据用户ID查询用户的借书记录
     * @param userId
     * @return
     */
    public List<LendRecordModel> findLendRecordByUserId(String userId){
        List<LendRecordModel> list = new ArrayList<LendRecordModel>();
        String sql = "SELECT l.*,b.bookname,ca.categoryname FROM lendrecord l,book b ,category ca WHERE l.bookid=b.id AND b.categoryid=ca.categoryid  ";
        if(WebSiteUtil.isNotEmpty(userId))
            sql +="  AND l.userid=? " ;
        sql+=" ORDER BY l.createtime desc";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DbConnection.getInstance().getConnection();
            ps = conn.prepareStatement(sql);
            //userId 大于0时作为有效的查询条件，否则将不作为查询条件
            if(WebSiteUtil.isNotEmpty(userId))
                ps.setString(1,userId);
            rs = ps.executeQuery();
            while(rs.next()){
                LendRecordModel m = new LendRecordModel();
                m.setId(rs.getInt("id"));
                m.setBookId(rs.getInt("bookid"));
                m.setBookName(rs.getString("bookname"));
                m.setCategoryName(rs.getString("categoryname"));
                m.setUserId(rs.getString("userid"));
                java.sql.Timestamp d = rs.getTimestamp("createtime");
                m.setCreateTime(new java.util.Date(d.getTime()));
                list.add(m);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(rs!=null){
                try{
                    rs.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try{
                    ps.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        return list;
    }


}
